Extracting data from XML in Sql Server column

Question

I've just discovered (by necessity) that TSQL has some functionality to extract data from columns that contain XML. I've got a Sql Server column that contains XML data (though the column type isn't xml...it's varchar). I'm supposed to extract a subset of the data from the column. A simplified entry would look something like this:

declare @Table as table(id char(1), datacolumn xml)

insert into @table
select 'a', '<root><vnode v="5" /><vnode v="8" /></root>'

insert into @table
select 'b', '<root><vnode v="7" /></root>'


id   datacolumn
---  -----------------------------------------
a    <root><vnode v="5" /><node v="8" /></root>
b    <root><vnode v="7" /></root>

So, I'd like to come up with a query that would return something along the lines of:

id   data
---  ----
a    5
a    8
b    7

I've gotten to the point where I'm able to get some information...but I'm missing some concepts.

select id, T.c.value('node[1]', 'varchar(100)') mdata
from @table
cross apply datacolumn.nodes('/root') AS T(c)

Problems:

This will get me the value inside the vnode (which is empty), but I need the value for the attribute marked "v".
Also, because I've got [1] in there, I'm getting the first node...but I want all of them...but haven't groked the concept for that yet.

Quassnoi · Accepted Answer · 2010-01-27 15:58:52Z

6

SELECT  id, T.c.value('@v', 'NVARCHAR(MAX)')
FROM    @table
CROSS APPLY
        datacolumn.nodes('/root/vnode') AS T(c)

The datacolumn.nodes('/root/vnode') returns all instances of /root/vnode for each XML.

There are 2 of those in a and 1 in b.

value() returns the value of an XPath expression relative to the node returned by the previous step.

Since you want to return the value of an attribute of the node in question, you need just prepend the attribute name with a @ (which tells that you want a child attribute, not a child node).

edited Jan 27, 2010 at 15:58

answered Jan 27, 2010 at 15:49

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beska Over a year ago

I knew this should be simple, and that I'd be missing something elementary! Thanks!

Collectives™ on Stack Overflow

Extracting data from XML in Sql Server column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related